﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using bdnt_pets_system_Entity;
using System.Data;
using System.Data.SqlClient;

namespace bdnt_pets_system_Bo
{
    public class TeamManager
    {
        //AccessHelperSQL accessHelper = new AccessHelperSQL();
        AccessHelper accessHelper = AccessHelper.GetInstance();

        public List<Tbl_Team> ConvertTableToObjs(DataTable dt)
        {
            List<Tbl_Team> returnlist = new List<Tbl_Team>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_Team
                {
                    Id = dr["Id"] == DBNull.Value ? 0 : Int32.Parse(dr["Id"].ToString()),
                    TeamNo = dr["TeamNo"] == DBNull.Value ? "" : dr["TeamNo"].ToString(),
                    TeamName = dr["TeamName"] == DBNull.Value ? "" : dr["TeamName"].ToString(),
                    TeamType = dr["TeamType"] == DBNull.Value ? 0 : Int32.Parse(dr["TeamType"].ToString()),
                    DeptNo = dr["DeptNo"] == DBNull.Value ? "" : dr["DeptNo"].ToString(),
                    Remark = dr["Remark"] == DBNull.Value ? "" : dr["Remark"].ToString()
                });
            }
            return returnlist;
        }

        public List<Tbl_Team> SelectTeam(string TNme)
        {
            var sql = string.Format(@"select * from Tbl_Team");
            if (!string.IsNullOrEmpty(TNme))
            {
                sql = string.Format(@"select * from Tbl_Team where TeamName like '%{0}%'", TNme);
            }
            var dt = accessHelper.ExcuteDataTable(sql);
            return ConvertTableToObjs(dt);
        }

        public Tbl_Team GetTeam(Int32 objId)
        {
            Tbl_Team tt = new Tbl_Team();
            string sql = string.Format("select * from Tbl_Team where Id={0}", objId);
            var dt = accessHelper.ExcuteDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                tt = new Tbl_Team()
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    TeamNo = dr["TeamNo"] == DBNull.Value ? "" : dr["TeamNo"].ToString(),
                    TeamName = dr["TeamName"] == DBNull.Value ? "" : dr["TeamName"].ToString(),
                    TeamType = dr["TeamType"] == DBNull.Value ? 0 : Int32.Parse(dr["TeamType"].ToString()),
                    DeptNo = dr["DeptNo"] == DBNull.Value ? "" : dr["DeptNo"].ToString(),
                    Remark = dr["Remark"] == DBNull.Value ? "" : dr["Remark"].ToString()
                };
            }
            return tt;
        }

        public bool SaveTeam(Tbl_Team tt)
        {
            string sql = "";
            if (tt.Id == 0)
            {
                sql = string.Format(@"INSERT INTO Tbl_Team(TeamNo,TeamName,TeamType,DeptNo,Remark)
             VALUES('{0}','{1}','{2}','{3}','{4}')", tt.TeamNo, tt.TeamName, tt.TeamType, tt.DeptNo, tt.Remark);
                return accessHelper.ExcultData(sql);
            }
            else
            {
                sql = string.Format(@"UPDATE Tbl_Team Set TeamNo='{0}',TeamName='{1}',TeamType='{2}',DeptNo='{3}',Remark='{4}'  WHERE Id='{5}'",
                    tt.TeamNo, tt.TeamName, tt.TeamType, tt.DeptNo, tt.Remark, tt.Id);
                return accessHelper.ExcultData(sql);
            }
        }

        public bool DeleteTeam(Int32 objId)
        {
            string sql = string.Format(@"delete from [Tbl_Team] where Id={0}", objId);
            return accessHelper.ExcultData(sql);
        }


        #region ys
        public List<Tbl_TeamMemembers> GetTeamMemembers()
        {
            var sql = string.Format(@"select te.Id TeamId,te.TeamName,te.en_TeamName,emp.Id EmpId,emp.EmpName,tm.IsManager from Tbl_Team te 
             left join Tbl_TeamMemembers tm on te.Id=tm.TeamId 
             left join Tbl_Employee emp on emp.Id=tm.EmpId ");

            var dt = accessHelper.ExcuteDataTable(sql);
            List<Tbl_TeamMemembers> returnlist = new List<Tbl_TeamMemembers>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_TeamMemembers
                {
                    EmpId = dr["EmpId"] == DBNull.Value ? 0 : Int32.Parse(dr["EmpId"].ToString()),
                    EmpName = dr["EmpName"] == DBNull.Value ? "" : dr["EmpName"].ToString(),
                    en_TeamName = dr["en_TeamName"] == DBNull.Value ? "" : dr["en_TeamName"].ToString(),
                    TeamName = dr["TeamName"] == DBNull.Value ? "" : dr["TeamName"].ToString(),
                    IsManager = dr["IsManager"] == DBNull.Value ? false : dr["IsManager"].ToString() == "1" ? true : false,
                    TeamId = dr["TeamId"] == DBNull.Value ? 0 : Int32.Parse(dr["TeamId"].ToString())
                });
            }
            return returnlist;
        }
        #endregion

    }
}
